﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupFull]
#-- Purpose:		Performs a full backup of a database.  Chooses between
#--					LiteSpeed if available, otherwise standard Backup
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	master.dbo.xp_backup_database
#-- Called By:		[dbo].[spBackupFullAll]
#--					[dbo].[spBackupDifferential]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupFull]
(
	@database_name	sysname,
	@verify			bit					= 1,
	@label			nvarchar(128)		= NULL,
	@ignore_skip	bit					= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@dir varchar(8000), @sCommand varchar(8000), @file_name varchar(8000),
			@offline bit, @current_date datetime, @job_name_filter varchar(125)
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupFull'

--- Ensure the database skips backup if configured not to backup
IF ISNULL(@ignore_skip, 0) = 0 
	AND EXISTS (SELECT TOP 1 database_name FROM dbo.vwDatabase_DoNotBackup WHERE database_name = @database_name)
  BEGIN
	RETURN
  END

--- @database_name cannot be tempdb
SELECT	@database_name = LTRIM(RTRIM(ISNULL(@database_name, '')))
IF @database_name = 'tempdb'
  BEGIN
	PRINT 'tempdb database cannot be backed up (' + @database_name + ')'
	RETURN
  END

--- @database_name is a mandatory fields
IF @database_name = ''
  BEGIN
	RAISERROR ('The name of the database to backup is required (@database_name)', 16, 1)
	RETURN
  END

--- @database_name must be an existing database
IF NOT EXISTS (SELECT TOP 1 name FROM master.sys.databases WHERE name = @database_name)
  BEGIN
    RAISERROR ('The requested database does not exist', 16, 1)
    RETURN
  END

--- Snapshot databases cannot be backed up
IF EXISTS (SELECT TOP 1 name FROM master.sys.databases WHERE name = @database_name AND source_database_id IS NOT NULL)
  BEGIN
	PRINT 'Snapshot databases cannot be backed up (' + @database_name + ')'
    RETURN
  END

BEGIN TRY
	--- Get Current date/time stamp and set base job name
	SELECT	@current_date = GETDATE(),
			@job_name_filter = 'DBA: Backup Full'
	
	--- Ensure the directory exists  (xp)
	EXEC	dbo.spGetBackupDirectory @database_name = @database_name, @backup_directory_name = @dir OUTPUT 
	EXEC	dbo.spCreateDirectory @dir

	--- Get File Name
	EXEC	dbo.spGetFullBackupFileName @database_name = @database_name, @backup_directory_name = @file_name OUTPUT
	EXEC	@offline = dbo.spIsDatabaseOffline @database_name

	--- Determine if there is a label for the job execution
	IF @label IS NULL
		SELECT		@label = REPLACE(ctm_label, '''', '')
		FROM		dbo.tblControlmExecution (READUNCOMMITTED)
		WHERE		ctm_job_name LIKE (@job_name_filter + '%')
					AND ctm_date_started <= @current_date
		ORDER BY	ROW_NUMBER() OVER(ORDER BY ctm_job_name DESC)

	IF @offline = 0 AND @database_name <> 'tempdb'
	  BEGIN
		--- Build the command to backup the database
		IF EXISTS(select * from master.dbo.sysobjects where id = object_id(N'[master].[dbo].[xp_backup_database]'))
		  BEGIN
			SELECT	@sCommand = 'EXEC master.dbo.xp_backup_database @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''

			IF @label IS NOT NULL
				SELECT	@sCommand = @sCommand + ', @desc=''' + @label + ''''
		  END
		ELSE
		  BEGIN
			SELECT	@sCommand = 'BACKUP DATABASE [' + @database_name + '] TO DISK = ''' + @file_name + ''''

			--- Set the backup label
			IF @label IS NOT NULL
			  BEGIN
				SELECT	@sCommand = @sCommand + ' WITH NAME = ''' + @label + ''''

				IF @verify = 1
				  BEGIN
					SELECT @sCommand = @sCommand + ', CHECKSUM; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
				  END
			  END
			ELSE IF @verify = 1
			  BEGIN
				SELECT @sCommand = @sCommand + ' WITH CHECKSUM; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
			  END
		  END

		--- Execute the command (backup the database)
		IF LEN(@sCommand) > 0
		  BEGIN
			EXEC	(@sCommand)
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		  END
	  END
END TRY
BEGIN CATCH
	--- Log the error - if possible
	DECLARE @sResults varchar(8000)
	SELECT @sResults = '[spBackupFull] The database [' + @database_name + '] failed to backup (' + @sCommand + ').' 
	IF ERROR_NUMBER() IS NOT NULL
		SELECT @sResults = @sResults + CHAR(13)
						+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
						+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
						+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
						+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

	--- Print the error message to sysout
	PRINT @sResults

	--- Insert the error in the database
	IF @sResults IS NOT NULL
		BEGIN
		INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database_name, GETDATE(), 'backup_full', 0, @sResults)

		--- Must raise an error so sqlcmd catches the severity level
		RAISERROR (@sResults, 16, 1)
		RETURN
		END
	ELSE
		BEGIN
		--- Must raise an error so sqlcmd catches the severity level
		SELECT @sResults = '[spBackupFull] The database [' + ISNULL(@database_name, '') + '] failed to backup (' + ISNULL(@sCommand, '') + ').'
		RAISERROR (@sResults, 16, 1) WITH LOG
		RETURN
		END
END CATCH

SET NOCOUNT OFF
